﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Module;
using Cit.CDE.Net.Module;
using cde.SystemCommon;
using Cit.CDE.Net.Service.ModuleFactory;
using Cit.CDE.Net.Module.Commons;
using System.IO;
using System.Text;
using UserControl;
using System.Data;
using System.Reflection;

/// <summary>
/// 商机报备导出
/// </summary>
public partial class main_sales_BusinessExport : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string chkID = Util.objectToString(Page.Request.QueryString["chkID"]);
        if (chkID != "")
        {
            ExportBusiness(chkID);
            return;
        }
    }

    /// <summary>
    /// 导出
    /// </summary>
    protected void ExportBusiness(string chkID){
        DataTable businessTable = this.GetBusinessDataTable(chkID);
        DataTable detailsTable = this.GetBusinessDetailDataTable(chkID);
        DataTable followTable = this.GetBusinessFollowDataTable(chkID);

        string filename = string.Empty;
        StringBuilder templateFileName = new StringBuilder();
        templateFileName.Append(System.Configuration.ConfigurationManager.AppSettings["Business"]);
        templateFileName.Append("");
        templateFileName.Append("商机报备");
        templateFileName.Append(".xls");
        filename = System.Web.HttpContext.Current.Server.MapPath(templateFileName.ToString());
        writeTableDataToExcel_Self(filename, businessTable, detailsTable, followTable);

        excelDownload(filename);    
    }

    
    /// <summary>
    /// 取得 商机报备DataTable
    /// </summary>
    /// <param name="chkID"></param>
    /// <returns></returns>
    private DataTable GetBusinessDataTable(string chkID)
    {
        string selectSQL = this.GetBusinessSQL(chkID);
        DataTable table = Util.loadSql(selectSQL);
        if (table.Rows.Count == 1)
        {
            //城市名称
            string ctcode = Util.objectToString(table.Rows[0]["CITY_ID__CTCODE"]);
            string ctlevel = Util.objectToString(table.Rows[0]["CITY_ID__CITY_LEVEL"]);
            string cname = Util.getCityName(ctcode, ctlevel);
            table.Rows[0]["CITY_ID__CITY_NAME"] = cname;

            //采购形式
            CDEEntityManager EM=CDEEMFactory.getEMByFullName(WEBConstants.EM_BUSINESS_MANAGE);
            string PURCHASE=Util.objectToString(table.Rows[0]["PURCHASE"]);
            string purchaseName=Util.getCandidate(EM, "PURCHASE", PURCHASE);
            table.Rows[0]["PURCHASE"] = purchaseName;

            //报备状态
            string STATUS = Util.objectToString(table.Rows[0]["STATUS"]);
            string STATUS_NAM = Util.getCandidate(EM, "STATUS", STATUS);
            table.Rows[0]["STATUS"] = STATUS_NAM;

          
        }

        return table;
    }


    /// <summary>
    /// 取得 商机报备详细DataTable
    /// </summary>
    /// <param name="chkID"></param>
    /// <returns></returns>
    private DataTable GetBusinessDetailDataTable(string chkID)
    {
        string selectSQL = this.GetBusinessDetailSQL(chkID);
        DataTable table = Util.loadSql(selectSQL);
        return table;
    }

    /// <summary>
    /// 取得 商机报备跟进DataTable
    /// </summary>
    /// <param name="chkID"></param>
    /// <returns></returns>
    private DataTable GetBusinessFollowDataTable(string chkID)
    {
        string selectSQL = this.GetBusinessFollowSQL(chkID);
        DataTable table = Util.loadSql(selectSQL);
        return table;
    }

      /// <summary>
    /// SQL 商机报备
    /// </summary>
    /// <param name="chkID"></param>
    /// <returns></returns>
    private string GetBusinessSQL(string chkID)
    {
     StringBuilder selectString = new StringBuilder();
     selectString.AppendFormat(@"SELECT TOP 101  STATUS,REPORT,H364204096.ID CDE_DATAROW_ID,H364204096.BS_ID BS_ID,H364204096.PROJECT_NAME PROJECT_NAME,H364204096.BS_NAME BS_NAME,H364204096.CITY_ID CITY_ID,H1840881060.CITY_NAME CITY_ID__CITY_NAME,dbo.DATE_FORMAT(H364204096.ORDER_DATE,'YYYY/MM/DD') ORDER_DATE,H364204096.TOTAL TOTAL,H364204096.INSERT_USER INSERT_USER,dbo.DATE_FORMAT(H364204096.INSERT_DATE,'YYYY/MM/DD') INSERT_DATE,dbo.DATE_FORMAT(H364204096.UPDATE_DATE,'YYYY/MM/DD') UPDATE_DATE,H364204096.UPDATE_USER UPDATE_USER,H364204096.MEMO MEMO,H1840881060.CTCODE CITY_ID__CTCODE,H1840881060.CITY_LEVEL CITY_ID__CITY_LEVEL,H364204096.AREA_ID AREA_ID,H364204096.BS_CONTCAT BS_CONTCAT,H364204096.BS_PHONE BS_PHONE,H364204096.PURCHASE PURCHASE,H364204096.CHANCE CHANCE,dbo.DATE_FORMAT(H364204096.BIDDING_DATE,'YYYY/MM/DD') BIDDING_DATE,H364204096.COMPETITOR COMPETITOR,H364204096.SUPPORT SUPPORT,H364204096.PARTNER_ID PARTNER_ID,H890006551.AREA_NAME AREA_ID__AREA_NAME,H1324423364.COMPANY_NAME PARTNER_ID__COMPANY_NAME,H1939739917.USER_NAME INSERT_USER__USER_NAME,H890006551.DEL_FLG AREA_ID__DEL_FLG,'' SELECT_END  FROM BUSINESS H364204096 LEFT JOIN CITYKEY H1840881060 ON H1840881060.M_TABLE_NAME IN ('CITYKEY') AND H364204096.CITY_ID = H1840881060.ID LEFT JOIN AREA H890006551 ON H890006551.M_TABLE_NAME IN ('AREA') AND H364204096.AREA_ID = H890006551.ID LEFT JOIN PARTNER H1324423364 ON H1324423364.M_TABLE_NAME IN ('PARTNER') AND H364204096.PARTNER_ID = H1324423364.ID LEFT JOIN USERS H1939739917 ON H1939739917.M_TABLE_NAME IN ('USERS') AND H364204096.INSERT_USER = H1939739917.ID WHERE H364204096.M_TABLE_NAME IN ('BUSINESS')  AND (H364204096.ID = {0} )", chkID); 
        return selectString.ToString();
    }

    /// <summary>
    /// SQL 商机报备详细
    /// </summary>
    /// <param name="chkID"></param>
    /// <returns></returns>
    private string GetBusinessDetailSQL(string chkID)
    {
        StringBuilder selectString = new StringBuilder();
        selectString.AppendFormat(
     @"SELECT P.[PRODUCT_NAME]
      ,D.[AMOUNT]
      ,D.[PRICE]
      ,D.[PRICE2]
      ,D.[MEMO]
      FROM [BUSINESS_DETAILS] D,PRODUCT P      
      WHERE 1=1
      AND BS_ID={0} 
      AND P.ID=D.PRODUCT_ID
      ORDER BY D.ID 
            ", chkID);
        return selectString.ToString();
    }

     /// <summary>
    /// SQL 商机报备跟进
    /// </summary>
    /// <param name="chkID"></param>
    /// <returns></returns>
    private string GetBusinessFollowSQL(string chkID)
    {
        StringBuilder selectString = new StringBuilder();
        selectString.AppendFormat(
     @"SELECT F.INSERT_DATE,U.USER_NAME,F.MEMO,F.PLANS
        FROM BUSINESS_FOLLOW F,USERS U
        WHERE F.INSERT_USER=U.ID
        AND F.BS_ID={0}
            ", chkID);
        return selectString.ToString();
    }

    /// <summary>
    /// AGENT_SELF模板导出
    /// </summary>
    /// <param name="templateFileName">路径+名称</param>
    /// <param name="dataTable">转换数据</param>
    /// <returns></returns>
    public bool writeTableDataToExcel_Self(string templateFileName, DataTable businessDataTable, System.Data.DataTable detailDataTable,DataTable followDataTable)
    {
        if (String.IsNullOrEmpty(templateFileName))
        {
            return false;
        }

        //创建Application对象 
        Microsoft.Office.Interop.Excel.Application xlsApp = null;
        try
        {
            xlsApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlsApp == null)
            {
                return false;
            }

            xlsApp.Visible = false;
            string filepath = @"~/Downloads/Template/Business.xls";
            filepath = System.Web.HttpContext.Current.Server.MapPath(filepath);

            //得到WorkBook对象, 打开已有的文件
            Microsoft.Office.Interop.Excel.Workbook xlsBook = xlsApp.Workbooks.Open(filepath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
 
            //指定要操作的Sheet
            Microsoft.Office.Interop.Excel.Worksheet xlsSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlsBook.Sheets[1];

            string name = "Sheet1";
            xlsSheet.Name = name;

            // 创建商机报备缓存数据
            object[,] business_objData = new object[19, 1];
            int col = 0;
            int row = 0;
            business_objData[row++, col] = Util.objectToString(businessDataTable.Rows[0]["INSERT_DATE"]);
            business_objData[row++, col] = Util.objectToString(businessDataTable.Rows[0]["AREA_ID__AREA_NAME"]);
            business_objData[row++, col] = Util.objectToString(businessDataTable.Rows[0]["INSERT_USER__USER_NAME"]);            
            business_objData[row++, col] = Util.objectToString(businessDataTable.Rows[0]["PROJECT_NAME"]);
            business_objData[row++, col] = Util.objectToString(businessDataTable.Rows[0]["BS_NAME"]);
            business_objData[row++, col] = Util.objectToString(businessDataTable.Rows[0]["BS_CONTCAT"]);
            business_objData[row++, col] = Util.objectToString(businessDataTable.Rows[0]["BS_PHONE"]);
            business_objData[row++, col] = Util.objectToString(businessDataTable.Rows[0]["CITY_ID__CITY_NAME"]);
            business_objData[row++, col] = Util.objectToString(businessDataTable.Rows[0]["PURCHASE"]);
            business_objData[row++, col] = Util.objectToString(businessDataTable.Rows[0]["ORDER_DATE"]);
            business_objData[row++, col] = Util.objectToString(businessDataTable.Rows[0]["TOTAL"]);
            business_objData[row++, col] = Util.objectToString(businessDataTable.Rows[0]["BIDDING_DATE"]);
            business_objData[row++, col] = Util.objectToString(businessDataTable.Rows[0]["CHANCE"]);
            business_objData[row++, col] = Util.objectToString(businessDataTable.Rows[0]["PARTNER_ID__COMPANY_NAME"]);
            business_objData[row++, col] = Util.objectToString(businessDataTable.Rows[0]["COMPETITOR"]);
            business_objData[row++, col] = Util.objectToString(businessDataTable.Rows[0]["SUPPORT"]);
            business_objData[row++, col] = Util.objectToString(businessDataTable.Rows[0]["MEMO"]);
            business_objData[row++, col] = Util.objectToString(businessDataTable.Rows[0]["REPORT"]);
            business_objData[row++, col] = Util.objectToString(businessDataTable.Rows[0]["STATUS"]);
 
            Microsoft.Office.Interop.Excel.Range business_dataRange = xlsSheet.get_Range(xlsSheet.Cells[2, 2], xlsSheet.Cells[20, 2]);
            business_dataRange.Value2 = business_objData;



            // 创建商机报备明细缓存数据
            int colIndex = 0;
            int RowIndex = 0;
            int RowCount = detailDataTable.Rows.Count;
            int colCount = detailDataTable.Columns.Count;
            object[,] detail_objData = new object[RowCount, colCount];
            // 获取数据
            for (RowIndex = 0; RowIndex < RowCount; RowIndex++)
            {
                for (colIndex = 0; colIndex < colCount; colIndex++)
                {
                    detail_objData[RowIndex, colIndex] = detailDataTable.Rows[RowIndex][colIndex];
                }
            }
            //报备明细设值
            int startDetail = 23;//明细开始行
            Microsoft.Office.Interop.Excel.Range detail_dataRange = xlsSheet.get_Range(xlsSheet.Cells[startDetail, 1], xlsSheet.Cells[RowCount + startDetail - 1, colCount]);
            detail_dataRange.Value2 = detail_objData;


            

             // 创建商机报备明跟进存数据
            colIndex = 0;
            RowIndex = 0;
            RowCount = followDataTable.Rows.Count;
            colCount = followDataTable.Columns.Count;
            object[,] follow_objData = new object[RowCount, colCount];
            // 获取数据
            for (RowIndex = 0; RowIndex < RowCount; RowIndex++)
            {
                for (colIndex = 0; colIndex < colCount; colIndex++)
                {
                    follow_objData[RowIndex, colIndex] = followDataTable.Rows[RowIndex][colIndex];
                }
            }
            //报备跟进设值
            int startfollow = 36;//跟进开始行
            Microsoft.Office.Interop.Excel.Range follow_dataRange = xlsSheet.get_Range(xlsSheet.Cells[startfollow, 1], xlsSheet.Cells[RowCount + startfollow - 1, colCount]);
            follow_dataRange.Value2 = follow_objData;

            //保存，关闭
            xlsBook.Saved = true;
            xlsBook.SaveAs(templateFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            xlsBook.Close(false, Type.Missing, Type.Missing);
        }
        catch (Exception ex)
        {
            Log.SaveNote(ex.ToString());
        }
        finally
        {
            xlsApp.Quit();
            GC.Collect();
        }
        if (xlsApp != null) xlsApp.Quit();
        return true;

    }

    /// <summary>
    /// excel下载
    /// </summary>
    /// <param name="templateFileName"></param>
    private void excelDownload(string templateFileName)
    {
        //ファイルダウンロードダイアログを表示する
        FileInfo downloadFile = new FileInfo(templateFileName);
        if (downloadFile.Exists == true)
        {
            Response.Clear();
            Response.ClearHeaders();
            Response.Buffer = false;
            Encoding encodeShiftJIS = Encoding.GetEncoding("UTF-8");
            Response.ContentType = "application/octet-stream";
            Response.AppendHeader("Content-Disposition", "attachment;filename="
                + HttpUtility.UrlEncode(downloadFile.Name, encodeShiftJIS));
            Response.AppendHeader("Content-Length", downloadFile.Length.ToString());
            Response.WriteFile(downloadFile.FullName);
            Response.Flush();
            downloadFile.Delete();
            Response.End();
        }
    } 
      
}
